package com.aizhixin.test.errolAlmost.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import com.aizhixin.test.errolAlmost.dto.SteamDTO;
import com.aizhixin.test.errolAlmost.dto.steamoptionDTO;

import ch.qos.logback.core.joran.conditional.IfAction;

@Component
public class SteamAllDAO {

	
	@Autowired
	private JdbcTemplate jdbcTemplate;
	/*
	 * 查找题目信息以及用户当时选错的选项
	 * SELECT p.STEM_ID,p.USER_ANSWER,q.CONTENT,q.ANSWER,q.KNOWLEDGE_AREA FROM t_question as q,answer_record as p WHERE q.ID=p.STEM_ID and p.USWER_THIS_STEM_RIGHT_OR_NOT=0 and USER_ID=2 ;
	 * 
	 */
	public List<SteamDTO> findErrolAllByUserId(Long userid){
		final String sql="SELECT p.USWER_THIS_STEM_RIGHT_OR_NOT, p.STEM_ID,p.USER_ANSWER,q.CONTENT,q.ANSWER,q.KNOWLEDGE_AREA,p.ASWER_TIME FROM t_question AS q,	answer_record AS p WHERE q.ID = p.STEM_ID AND p.USWER_THIS_STEM_RIGHT_OR_NOT = 0 AND USER_ID =? ORDER BY p.ASWER_TIME DESC ";
		List<SteamDTO> list = jdbcTemplate.query(sql,
				new Object[] {userid},
				new int[] {Types.LONGVARCHAR},
				new RowMapper<SteamDTO>() {
			public SteamDTO mapRow(ResultSet rs,
					int rowNum)throws SQLException{
				SteamDTO test=new SteamDTO();
				if (rs.getInt("USWER_THIS_STEM_RIGHT_OR_NOT") == 1) {
					test.setYesoOrNo(true);
				} else {
					test.setYesoOrNo(false);
				}
				test.setQuestionID(rs.getLong("STEM_ID"));
				test.setAnswer(rs.getString("ANSWER"));
				test.setContent(rs.getString("CONTENT"));
				test.setUserOption(rs.getString("USER_ANSWER"));
				test.setKownledge(rs.getString("KNOWLEDGE_AREA"));
				test.setAnserTime(rs.getDate("ASWER_TIME"));
				test.setOptions(findBySteamId(test.getQuestionID()));
				
				return test;
			}});
		
		return list;
	
	}
	/**
	 * 查看题所有的答题情况
	 * @param userid
	 * @return
	 */
	public List<SteamDTO> findAllByUserId(Long userid){
		final String sql="SELECT p.USWER_THIS_STEM_RIGHT_OR_NOT,p.STEM_ID,p.USER_ANSWER,q.CONTENT,q.ANSWER,q.KNOWLEDGE_AREA,p.ASWER_TIME FROM t_question AS q,	answer_record AS p WHERE q.ID = p.STEM_ID  AND USER_ID =? ORDER BY p.ASWER_TIME DESC ";
		List<SteamDTO> list = jdbcTemplate.query(sql,
				new Object[] {userid},
				new int[] {Types.LONGVARCHAR},
				new RowMapper<SteamDTO>() {
			public SteamDTO mapRow(ResultSet rs,
					int rowNum)throws SQLException{
						SteamDTO test = new SteamDTO();
						if (rs.getInt("USWER_THIS_STEM_RIGHT_OR_NOT") == 1) {
							test.setYesoOrNo(true);
						} else {
							test.setYesoOrNo(false);
						}
						test.setQuestionID(rs.getLong("STEM_ID"));
						test.setAnswer(rs.getString("ANSWER"));
						test.setContent(rs.getString("CONTENT"));
						test.setUserOption(rs.getString("USER_ANSWER"));
						test.setKownledge(rs.getString("KNOWLEDGE_AREA"));
						test.setAnserTime(rs.getDate("ASWER_TIME"));
						test.setOptions(findBySteamId(test.getQuestionID()));
						
						return test;
					}
				});
		
		return list;
	
	}
		/**
		 * 求这个用户对的所有题目
		 * @param userid
		 * @return
		 */
		public List<SteamDTO> findRightAllByUserId(Long userid){
			final String sql="SELECT  p.USWER_THIS_STEM_RIGHT_OR_NOT,p.STEM_ID,p.USER_ANSWER,q.CONTENT,q.ANSWER,q.KNOWLEDGE_AREA,p.ASWER_TIME FROM t_question AS q,	answer_record AS p WHERE q.ID = p.STEM_ID AND p.USWER_THIS_STEM_RIGHT_OR_NOT = 1 AND USER_ID =? ORDER BY p.ASWER_TIME DESC ";
			List<SteamDTO> list = jdbcTemplate.query(sql,
					new Object[] {userid},
					new int[] {Types.LONGVARCHAR},
					new RowMapper<SteamDTO>() {
				public SteamDTO mapRow(ResultSet rs,
						int rowNum)throws SQLException{
					SteamDTO test=new SteamDTO();
					if (rs.getInt("USWER_THIS_STEM_RIGHT_OR_NOT") == 1) {
						test.setYesoOrNo(true);
					} else {
						test.setYesoOrNo(false);
					}
					test.setQuestionID(rs.getLong("STEM_ID"));
					test.setAnswer(rs.getString("ANSWER"));
					test.setContent(rs.getString("CONTENT"));
					test.setUserOption(rs.getString("USER_ANSWER"));
					test.setKownledge(rs.getString("KNOWLEDGE_AREA"));
					test.setOptions(findBySteamId(test.getQuestionID()));
					test.setAnserTime(rs.getDate("ASWER_TIME"));
					return test;
				}});
			return list;
			}
	/*
	 * 根据题号返回对应题目的选项
	 */
	public List<steamoptionDTO> findBySteamId(Long x){
		final String sql = "SELECT q.OPTION,q.OPTION_CONTENT FROM t_question_option q WHERE q.QUESTION_ID=?";
		List<steamoptionDTO> list = jdbcTemplate.query(sql, 
				new Object[]{ x }, 
				new int[] {Types.INTEGER},
				new RowMapper<steamoptionDTO>() {
			public steamoptionDTO mapRow(ResultSet rs, 
					int rowNum) throws SQLException {
				steamoptionDTO test = new steamoptionDTO();
				test.setOptionContent(rs.getString("OPTION_CONTENT"));
				test.setOption(rs.getString("OPTION"));
			
				return test;
			}
		});
		if (null != list && list.size() > 0) {
			return list;
		}
		return null; 
	}
	public List<SteamDTO> findSteamByImformation(Long userId, String importImfomation) {
		final String sql="SELECT p.`USWER_THIS_STEM_RIGHT_OR_NOT`,p.`STEM_ID`,p.`USER_ANSWER`,"
				+ "q.`CONTENT`,q.`ANSWER`,q.`KNOWLEDGE_AREA`,p.`ASWER_TIME` FROM `t_question` AS q,"
				+ "`answer_record` AS p WHERE q.`ID`= p.`STEM_ID` AND p.`USER_ID`=? and q.`CONTENT` LIKE ? "; 
		List<SteamDTO> list = jdbcTemplate.query(sql,
				new Object[] {userId,"%" + importImfomation + "%"},
				new RowMapper<SteamDTO>() {
			public SteamDTO mapRow(ResultSet rs,
					int rowNum)throws SQLException{
				SteamDTO test=new SteamDTO();
				if (rs.getInt("USWER_THIS_STEM_RIGHT_OR_NOT") == 1) {
					test.setYesoOrNo(true);
				} else {
					test.setYesoOrNo(false);
				}
				test.setQuestionID(rs.getLong("STEM_ID"));
				test.setAnswer(rs.getString("ANSWER"));
				test.setContent(rs.getString("CONTENT"));
				test.setUserOption(rs.getString("USER_ANSWER"));
				test.setKownledge(rs.getString("KNOWLEDGE_AREA"));
				test.setOptions(findBySteamId(rs.getLong("STEM_ID")));
				test.setAnserTime(rs.getDate("ASWER_TIME"));
				return test;
			}});
		return list;
		}
	}
	

